import sqlite3
import datetime


#插入数据
def insert(bh,wd,sd,xh,gm,zd,yw,date,time):
    conn = sqlite3.connect("bd.db")
    c = conn.cursor()

    c.execute("INSERT INTO ls (id,bh,wd,sd,xh,gm,zd,yw,date,time) VALUES (NULL,?,?,?,?,?,?,?,?,?)",(bh,wd,sd,xh,gm,zd,yw,date,time))
    conn.commit()
    conn.close()


#通过id查询最后一条数据
def select_new_issue():
    conn = sqlite3.connect("bd.db")
    c = conn.cursor()
    list1 = []
    list = c.execute("SELECT * FROM ls ORDER BY id DESC limit 1") #按降序查询最后一条数据
    for a in list:
        list1.append(a)
    conn.commit()
    conn.close()
    return list1


def select_history(date,time):
    st = datetime.datetime.strptime(time,"%H:%M:%S")
    st = (st + datetime.timedelta(hours=1))
    st = st.strftime("%H:%M:%S")
    conn = sqlite3.connect("bd.db")
    c = conn.cursor()
    list1 = []
    list = c.execute("SELECT * from ls where date='"+date+"' and time >='"+time+"' and time<'"+st+"'")
    for row in list:
        list1.append(row)
    if len(list1) == 0:
        list1=[
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
                [0,0,0,0,0,0,0,0,0,0],
            ]
    conn.commit()
    conn.close()
    return list1


def select_data(select):
    if select=="温度":
        select_temp = 'wd'
    if select=="湿度":
        select_temp = 'sd'
    if select=="信号":
        select_temp = 'xh'
    if select=="光敏":
        select_temp = 'gm'
    if select=="震动":
        select_temp = 'zd'
    if select=="烟雾":
        select_temp = 'yw'

    conn = sqlite3.connect("bd.db")
    c = conn.cursor()
    list1 = []
    list2 = []
    list = c.execute("SELECT "+select_temp+",time FROM ls ORDER BY id DESC limit 60") #按降序查询最后一条数据
    for a in list:
        list1.append(a[0])
        list2.append(a[1])
    conn.commit()
    conn.close()
    return list1,list2



# delete from TableName;  //清空数据
# update sqlite_sequence SET seq = 0 where name ='TableName';//自增长ID为0